import xlrd
import pymysql
from pymysql.cursors import DictCursor
from datetime import datetime
from tqdm import tqdm

# 数据库配置信息
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'pymysql',
    'charset': 'utf8mb4',
    'cursorclass': DictCursor
}

# 批处理大小
BATCH_SIZE = 10000


def create_table():
    """创建user_info表（如果不存在）"""
    try:
        # 建立数据库连接
        with pymysql.connect(**DB_CONFIG) as connection:
            # 创建游标对象
            with connection.cursor() as cursor:
                # 定义创建表的SQL语句
                sql = """
                CREATE TABLE IF NOT EXISTS user_info (
                    id INT(11),                  # 用户ID，整数类型
                    idcard VARCHAR(50),          # 身份证号码，字符串类型
                    username VARCHAR(50),        # 用户名，字符串类型
                    realname VARCHAR(50),        # 真实姓名，字符串类型
                    pwd VARCHAR(50),             # 密码，字符串类型
                    telphone VARCHAR(12),        # 电话号码，字符串类型
                    email VARCHAR(100),          # 电子邮箱，字符串类型
                    age INT(11),                 # 年龄，整数类型
                    sex VARCHAR(20),             # 性别，字符串类型
                    address VARCHAR(200),        # 地址，字符串类型
                    hiredate DATE,               # 入职日期，日期类型
                    sal DOUBLE(9,2),             # 工资，双精度浮点数，总位数9位，小数位2位
                    job VARCHAR(100),            # 职位，字符串类型
                    company VARCHAR(100)         # 公司，字符串类型
                )
                """
                # 执行SQL语句
                cursor.execute(sql)
            # 提交事务
            connection.commit()
        # 输出成功信息
        print("user_info表创建成功或已存在")
    except Exception as e:
        # 输出错误信息
        print(f"创建表时出错: {e}")
        # 抛出异常，便于上层调用者捕获和处理
        raise


def read_excel_data(file_path):
    """读取Excel文件数据"""
    try:
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)

        # 获取表头
        headers = [sheet.cell_value(0, col) for col in range(sheet.ncols)]

        # 定义Excel表头到数据库字段的映射
        field_mapping = {
            '序号': 'id',
            '工牌号':'idcard',
            '花名': 'username',
            '真实姓名': 'realname',
            '密码': 'pwd',
            '电话号码': 'telphone',
            '邮箱': 'email',
            '年龄': 'age',
            '性别': 'sex',
            '居住地址': 'address',
            '入职日期': 'hiredate',
            '薪资': 'sal',
            '职责': 'job',
            '外包公司': 'company'
        }

        # 初始化数据列表，用于存储处理后的Excel行数据
        data = []

        # 使用tqdm显示进度条，遍历Excel表格的每一行（从第1行开始，跳过表头）
        for row in tqdm(range(1, sheet.nrows), desc="读取Excel数据"):
            # 初始化当前行的数据字典
            row_data = {}

            # 遍历当前行的每一列
            for col in range(sheet.ncols):
                # 获取Excel表头名称
                excel_header = headers[col]

                # 检查当前Excel表头是否存在于映射关系中
                if excel_header in field_mapping:
                    # 获取Excel表头对应的数据库字段名
                    db_field = field_mapping[excel_header]
                    # 获取当前单元格的值
                    cell_value = sheet.cell_value(row, col)

                    # 处理日期类型数据
                    if sheet.cell_type(row, col) == 3:  # 日期类型（xlrd常量3表示日期）
                        # 将Excel日期格式转换为Python元组
                        date_tuple = xlrd.xldate_as_tuple(cell_value, workbook.datemode)
                        # 转换为标准日期字符串格式
                        cell_value = datetime(*date_tuple).strftime('%Y-%m-%d')

                    # 将处理后的数据存入当前行字典，键为数据库字段名
                    row_data[db_field] = cell_value

            # 将当前行数据添加到总数据列表中
            data.append(row_data)

        print(f"成功读取 {len(data)} 条数据")
        return data
    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        raise


def save_to_database(data):
    """将数据保存到数据库"""
    try:
        # 计算数据总量和批次数
        total_count = len(data)
        batches = (total_count + BATCH_SIZE - 1) // BATCH_SIZE

        # 建立数据库连接
        with pymysql.connect(**DB_CONFIG) as connection:
            # 遍历每个批次
            for batch in tqdm(range(batches), desc="写入数据库"):
                # 计算当前批次的数据范围
                start = batch * BATCH_SIZE
                end = min((batch + 1) * BATCH_SIZE, total_count)
                batch_data = data[start:end]

                # 准备SQL插入语句，使用字典占位符
                sql = """
                INSERT INTO user_info (
                    id, idcard, username, realname, pwd, 
                    telphone, email, age, sex, address, 
                    hiredate, sal, job, company
                ) VALUES (
                    %(id)s, %(idcard)s, %(username)s, %(realname)s, %(pwd)s, 
                    %(telphone)s, %(email)s, %(age)s, %(sex)s, %(address)s, 
                    %(hiredate)s, %(sal)s, %(job)s, %(company)s
                )
                """

                # 使用executemany批量执行SQL
                with connection.cursor() as cursor:
                    cursor.executemany(sql, batch_data)

                # 提交事务
                connection.commit()

        print(f"成功保存 {total_count} 条记录到数据库")
    except Exception as e:
        print(f"保存到数据库时出错: {e}")
        raise


def main():
    """主函数"""
    try:
        # 创建表
        create_table()

        # 读取Excel数据
        file_path = '../员工信息表.xls'  # 请替换为实际的Excel文件路径
        data = read_excel_data(file_path)

        # 保存到数据库
        save_to_database(data)

        print("所有数据已成功导入数据库！")
    except Exception as e:
        print(f"程序执行出错: {e}")


if __name__ == "__main__":
    main()